<?php

/**
 * @author Samrat Khan & Sadik Sarfaraz - 11:06:03 AM Aug 22, 2013 
 */
//require 'foo.config.php';
ob_start();
$host = 'localhost';
$user = 'root';
$pass = 'samraj77';
$db = 'final_warehouse';
//$table = 'stockin';
$file = 'Stockin_hand';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$values = mysql_query("
        SELECT 
            t1.inqty, t2.outqty, (IFNULL(t1.inqty,'0') - IFNULL(t2.outqty, '0')) totalHand, 
            t1.serialno, 
            (SELECT projectname FROM projects WHERE t1.project_id = projects.project_id) AS ProjectName, 
            TRIM(t1.productid), 
            TRIM(t1.description), 
            TRIM(t1.revisionstate), 
            (SELECT typename FROM shiptype WHERE t1.shiptype = shiptype.shiptype_id) AS ShipType,
            (SELECT measurement FROM uom WHERE t1.uom = uom.uom_id) AS UOM,
            TRIM(t1.mawb),
            TRIM(t1.hawb),
            TRIM(t1.receiveddate),
            TRIM(t1.invoicenumber),
            TRIM(t1.shipid),
            TRIM(t1.shipfrom),
            TRIM(t1.boxno),
            TRIM(t1.rma),
            (SELECT warehousename FROM warehouses WHERE t1.warehouse_id = warehouses.warehouse_id) AS WarehouseName,
            TRIM(t1.rackno),
            TRIM(t1.docketnumber),
            TRIM(t1.manufacturingdate),
            TRIM(t1.pickuprequestdate), 
            (SELECT employeename FROM employee WHERE t1.employee_id = employee.employee_id) AS EmployeeName,
            (SELECT engname FROM engineerinfo WHERE t1.eng_id = engineerinfo.eng_id) AS EngineerName,
            TRIM(t1.siteid),
            TRIM(t1.docketdate),
            TRIM(t1.remarks), 
            (SELECT username FROM wms_users WHERE t1.userid = wms_users.id) AS UserName,
            (SELECT cusname FROM customerinfo WHERE t1.cus_id = customerinfo.cus_id) AS CustomerName,
            t1.inserteddate
        FROM
            (
                SELECT *, SUM(in_quantity) inqty FROM stockin GROUP BY serialno
            ) t1 LEFT JOIN
            (
                SELECT serialno, SUM(out_quantity) outqty FROM stockout GROUP BY serialno
            ) t2
        ON t1.serialno = t2.serialno
        ");
$headings = array('INQTY', 'OUTQTY', 'TOTAL IN HAND', 'SerialNo', 'Project Name', 'ProductID', 'Description', 'Revision State', 'Ship Type', 'UOM', 'MAWB', 'HAWB', 'Received Date', 'Invoice Number', 'Ship ID', 'Ship From', 'Box No', 'RMA', 'Warehouse Name', 'Rack No', 'Docket Number', 'Manufacturing Date', 'Pickup Request Date', 'Employee Name', 'Engineer Name', 'Site ID', 'Docket Date', 'Remarks', 'Username', 'Customer Name', 'Inserted Date');
$i = 0;
foreach ($headings as $heading) {
    $csv_output .= $heading . ",";
    $i++;
}
$csv_output .= "\n";
while ($rowr = mysql_fetch_row($values)) {
    for ($j = 0; $j < $i; $j++) {
        $csv_output .= '"' . $rowr[$j] . '",';
    }
    $csv_output .= "\n";
}
ob_end_clean();

$filename = $file . "_" . date("Y-m-d_H-i", time());
header("Content-type: application/vnd.ms-excel");
//header("Content-type: text/csv");
header('Content-Length: ' . strlen($csv_output));
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=" . $filename . ".csv");
print $csv_output;
exit;
